﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using Dapper;
using System.Data;
using System.Text.RegularExpressions;

namespace _180FrameWork.DataBaseHelper.MySqlExtend
{
    public class MySqlJintDB : IJintDB
    {
        private Database _db;

        private DbConnection _Conn;

        private DbConnection Conn
        {
            get
            {
                if (_Conn == null)
                    _Conn = this._db.CreateConnection();
                return this._Conn;
            }
        }

        private string _paramChar = "?";

        public MySqlJintDB(string DBConnectionString)
        {
            _db = new GenericDatabase(DBConnectionString, MySql.Data.MySqlClient.MySqlClientFactory.Instance);
        }

        #region Dapper

        /// <summary>
        /// 查询单个
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public dynamic Find(string sql)
        {
            return this.Conn.Query(sql).FirstOrDefault();
        }
        /// <summary>
        /// 查询单个
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public dynamic Find(string sql, IDictionary<string, object> param)
        {
            return this.Conn.Query(sql, GetParams(sql, param)).FirstOrDefault();
        }
        /// <summary>
        /// 查询单个
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public dynamic Find(string sql, IDictionary<string, object> param, IDbTransaction tran)
        {
            return this.Conn.Query(sql, GetParams(sql, param), tran).FirstOrDefault();
        }
        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public IList<dynamic> Query(string sql)
        {
            return this.Conn.Query(sql).ToList();
        }
        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IList<dynamic> Query(string sql, IDictionary<string, object> param)
        {
            return this.Conn.Query(sql, GetParams(sql, param)).ToList();
        }
        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public IList<dynamic> Query(string sql, IDictionary<string, object> param, IDbTransaction tran)
        {
            return this.Conn.Query(sql, GetParams(sql, param), tran).ToList();
        }
        /// <summary>
        /// 查询分页
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public PageData<dynamic> Page(string sql)
        {
            return Page(sql, new Dictionary<string, object>());
        }
        /// <summary>
        /// 查询分页
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public PageData<dynamic> Page(string sql, IDictionary<string, object> param)
        {
            int page = 1; int rows = 20; string sort = ""; string order = ""; int total = 0; string SUM = "";
            foreach (KeyValuePair<string, object> item in param)
            {
                switch (item.Key)
                {
                    case "page":
                        page = Convert.ToInt32(item.Value);
                        break;
                    case "rows":
                        rows = Convert.ToInt32(item.Value);
                        break;
                    case "sort":
                        sort = Convert.ToString(item.Value);
                        break;
                    case "order":
                        order = Convert.ToString(item.Value);
                        break;
                    case "total":
                        total = Convert.ToInt32(item.Value);
                        break;
                    case "sum":
                        SUM = Convert.ToString(item.Value);
                        break;
                    //default:
                    //    dp.Add(item.Key, item.Value);
                    //    break;
                }
            }
            var dp = GetParams(sql, param);
            var p = new PageData<dynamic>();
            if (total != 0)
            {
                p.total = total;
            }
            else
            {
                string strTotalSql = "select count(*) from(" + sql + ") t";
                p.total = this.Conn.ExecuteScalar<int>(strTotalSql, dp);
                if (p.total == 0)
                    p.total = 1;           
            }
            p.totalPages = (p.total / rows) + (p.total % rows > 0 ? 1 : 0);
            //汇总
            if (SUM != "")
            {
                string strSumSql = "select " + SUM + " from(" + sql + ") t";
                p.footer = this.Conn.Query(strSumSql, dp).ToList();
            }
            //排序
            string orderby = "";
            if (sort != "")
            {
                orderby = " order by " + sort;
                if (order != "")
                {
                    orderby += " " + order;
                }
            }
			
			
			 //获取分页数据
            int beginrow = rows * (page - 1) + 1;
            int endrow = rows * page;
            dp.Add("beginrow", beginrow);
            dp.Add("endrow", endrow);
            dp.Add("pagesize", rows);
            string strDataSql = sql + orderby + " limit ?beginrow,?pagesize";
            p.rows = this.Conn.Query(strDataSql, dp).ToList();
            //p.footer=this.Conn.Query(
            return p;
			
           
        }
        /*
           public GridData<dynamic> Grid(string sql)
           {
               return Grid(sql, new Dictionary<string, object>());
           }

           public GridData<dynamic> Grid(string sql, IDictionary<string, object> param)
           {
               string sort = ""; string order = ""; string SUM = "";
               foreach (KeyValuePair<string, object> item in param)
               {
                   switch (item.Key)
                   {
                       case "sort":
                           sort = Convert.ToString(item.Value);
                           break;
                       case "order":
                           order = Convert.ToString(item.Value);
                           break;
                       case "sum":
                           SUM = Convert.ToString(item.Value);
                           break;
                       //default:
                       //    dp.Add(item.Key, item.Value);
                       //    break;
                   }
               }
               var dp = GetParams(sql, param);
               var p = new GridData<dynamic>();

               //汇总
               if (SUM != "")
               {
                   string strSumSql = "select " + SUM + " from(" + sql + ") t";
                   p.footer = this.Conn.Query(strSumSql, dp).ToList();
               }
               //排序
               string orderby = "";
               if (sort != "")
               {
                   orderby = " order by " + sort;
                   if (order != "")
                   {
                       orderby += " " + order;
                   }
               }
               p.rows = this.Conn.Query(sql, dp).ToList();
               //p.footer=this.Conn.Query(
               return p;
           }
       
           /// <summary>
           /// 查找标准数据结构
           /// </summary>
           /// <param name="sql"></param>
           /// <returns></returns>
           public DataTable QueryDataTable(string sql)
           {
               return QueryDataTable(sql, null);
           }
           /// <summary>
           /// 查找标准数据结构
           /// </summary>
           /// <param name="sql"></param>
           /// <param name="param"></param>
           /// <returns></returns>
           public DataTable QueryDataTable(string sql, IDictionary<string, object> param)
           {
               DbCommand command = _db.GetSqlStringCommand(sql);
               if (param != null)
               {
                   foreach (string strPara in param.Keys)
                   {
                       DbParameter para = command.CreateParameter();
                       para.ParameterName = strPara;
                       if (param[strPara] != null)
                           para.Value = param[strPara];
                       else
                           para.Value = DBNull.Value;
                       command.Parameters.Add(para);
                   }
               }
               DataSet ds = _db.ExecuteDataSet(command);
               return ds.Tables[0];
           }
            */
        /// <summary>
        /// 执行语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Exec(string sql)
        {
            return this.Conn.Execute(sql);
        }
        /// <summary>
        /// 执行语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Exec(string sql, IDictionary<string, object> param)
        {
            return this.Conn.Execute(sql, GetParams(sql, param));
        }
        /// <summary>
        /// 执行语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public int Exec(string sql, IDictionary<string, object> param, IDbTransaction tran)
        {
            return this.Conn.Execute(sql, GetParams(sql, param), tran);
        }
       
        /// <summary>
        /// 存储过程
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="param"></param>
        public IDictionary<string, object> Sp(string spName, IDictionary<string, object>[] param) { return null; }
     
        /// <summary>
        /// 开启事务
        /// </summary>
        /// <returns></returns>
        public IDbTransaction BeginTran()
        {
            return this.Conn.BeginTransaction();
        }
        /// <summary>
        /// 开启连接
        /// </summary>
        public void Open()
        {
            this.Conn.Open();
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        public void Close()
        {
            this.Conn.Close();
        }

        private DynamicParameters GetParams(string SQL, IDictionary<string, object> data)
        {
            var param = new DynamicParameters();
            if (this._paramChar != "")
            {
                var mapList = Regex.Matches(SQL, @"\" + this._paramChar + @"\w+").Cast<Match>().Select(m => m.Value).Distinct().ToArray();

                foreach (KeyValuePair<string, object> prop in data)
                {
                    if (mapList.Contains(this._paramChar + prop.Key))
                    {
                        if (prop.Value != null)
                        {
                            if (prop.Value.GetType().Name.ToUpper() == "STRING")
                            {
                                param.Add(prop.Key, new DbString() { Value = prop.Value.ToString(), IsAnsi = true });
                            }
                            else
                            {
                                param.Add(prop.Key, prop.Value);
                            }
                        }
                        else
                        {
                            param.Add(prop.Key, null);
                        }
                    }
                }
            }
            else
            {

                foreach (KeyValuePair<string, object> prop in data)
                {
                    if (prop.Value != null)
                    {
                        if (prop.Value.GetType().Name.ToUpper() == "STRING")
                        {
                            param.Add(prop.Key, new DbString() { Value = prop.Value.ToString(), IsAnsi = true });
                        }
                        else
                        {
                            param.Add(prop.Key, prop.Value.ToString());
                        }
                    }
                    else
                    {
                        param.Add(prop.Key, null);
                    }
                }
            }
            return param;
        }

        public IList<string> Match(string SQL, IDictionary<string, object> param)
        {
            IList<string> NoMatchList = new List<string>();
            if (this._paramChar != "")
            {
                var mapList = Regex.Matches(SQL, @"\" + this._paramChar + @"\w+").Cast<Match>().Select(m => m.Value).Distinct().ToArray();
                foreach (var p in mapList)
                {
                    string key = p.Replace(this._paramChar, "");
                    bool IsExist = false;
                    foreach (KeyValuePair<string, object> prop in param)
                    {
                        if (prop.Key == key)
                        {
                            IsExist = true;
                            break;
                        }
                    }
                    if (!IsExist)
                    {
                        NoMatchList.Add(key);
                    }
                }
            }
            return NoMatchList;
        }

        public void ChangeDatabase(string databaseName)
        {
            this.Conn.ChangeDatabase(databaseName);
        }
        #endregion


    
    }
}
